package t20250313_setparallelism;

public class Sql {


    public static String SQL = "create table o_xylive_mobilesdk_cycle (\n" +
            "    stime timestamp(3),\n" +
            "    c1 string,\n" +
            "    c2 string,\n" +
            "    c3 bigint,\n" +
            "    c4 bigint,\n" +
            "    c5 bigint,\n" +
            "    c6 bigint,\n" +
            "    proctime as proctime(),\n" +
            "    watermark for stime as stime - interval '10' second\n" +
            ") with (\n" +
            "    'connector' = 'datagen'" +
            ");\n" +
            "\n" +
            "create table r_i1_live_mobile_sdk_detail_stat (\n" +
            "    stime timestamp,\n" +
            "    ck_domain string,\n" +
            "    ck_s_c4 bigint,\n" +
            "    ck_s_c5 bigint\n" +
            ") with (\n" +
            "    'connector' = 'print'\n" +
            ");\n" +
            "insert into r_i1_live_mobile_sdk_detail_stat\n" +
            "select\n" +
            "    window_start as stime,\n" +
            "    c2 as domain,\n" +
            "    sum(c4) as s_c4,\n" +
            "    sum(c5) as s_c5\n" +
            "from table(tumble(table o_xylive_mobilesdk_cycle, descriptor(stime), interval '60' second))\n" +
            " WHERE c3 = 100" +
            "group by\n" +
            "    c2,\n" +
            "    window_start,\n" +
            "    window_end;\n";

    public static String SQL1 = "create table o_xylive_mobilesdk_cycle (\n" +
            "\t`stime`\ttimestamp(3),\n" +
            "\t`shostname`\tstring,\n" +
            "\t`cip`\tstring,\n" +
            "\t`cpro`\tint,\n" +
            "\t`cisp`\tstring,\n" +
            "\t`url`\tstring,\n" +
            "\t`domain`\tstring,\n" +
            "\t`app`\tstring,\n" +
            "\t`stream`\tstring,\n" +
            "\t`playguid`\tstring,\n" +
            "\t`ver`\tstring,\n" +
            "\t`play_time`\tstring,\n" +
            "\t`down_cdn`\tdouble,\n" +
            "\t`down_bkj`\tdouble,\n" +
            "\t`down_skj`\tdouble,\n" +
            "\t`down_fsdk`\tdouble,\n" +
            "\t`down_cdn_all`\tstring,\n" +
            "\t`down_bkj_all`\tstring,\n" +
            "\t`down_skj_all`\tstring,\n" +
            "\t`overlapped_length`\tstring,\n" +
            "\t`missrange_count`\tstring,\n" +
            "\t`fps_avg`\tstring,\n" +
            "\t`recv_chunk_count`\tstring,\n" +
            "\t`recv_piece_count`\tstring,\n" +
            "\t`use_piece_count`\tstring,\n" +
            "\t`recv_chunk_count_all`\tstring,\n" +
            "\t`recv_piece_count_all`\tstring,\n" +
            "\t`use_piece_count_all`\tstring,\n" +
            "\t`accu_bkj_ratio`\tstring,\n" +
            "\t`accu_skj_ratio`\tstring,\n" +
            "\t`accu_p2p_ratio`\tstring,\n" +
            "\t`down_cdn0`\tstring,\n" +
            "\t`down_cdn1`\tstring,\n" +
            "\t`down_cdn2`\tstring,\n" +
            "\t`down_cdn3`\tstring,\n" +
            "\t`down_cdn4`\tstring,\n" +
            "\t`down_cdn5`\tstring,\n" +
            "\t`down_cdn6`\tstring,\n" +
            "\t`down_cdn7`\tstring,\n" +
            "\t`down_cdn8`\tstring,\n" +
            "\t`bkj_conn_succ`\tdouble,\n" +
            "\t`bkj_conn_fail`\tdouble,\n" +
            "\t`bkj_ow_cps`\tstring,\n" +
            "\t`bkj_ow_uid`\tstring,\n" +
            "\t`skj_conn_succ`\tbigint,\n" +
            "\t`skj_conn_fail`\tbigint,\n" +
            "\t`skj_ow_cps`\tstring,\n" +
            "\t`skj_ow_uid`\tstring,\n" +
            "\t`mona_conn_fail`\tdouble,\n" +
            "\t`bkj_conn_succ_all`\tstring,\n" +
            "\t`bkj_conn_fail_all`\tstring,\n" +
            "\t`bkj_ow_cps_all`\tstring,\n" +
            "\t`bkj_ow_uid_all`\tstring,\n" +
            "\t`skj_conn_succ_all`\tstring,\n" +
            "\t`skj_conn_fail_all`\tstring,\n" +
            "\t`skj_ow_cps_all`\tstring,\n" +
            "\t`skj_ow_uid_all`\tstring,\n" +
            "\t`mona_conn_fail_all`\tstring,\n" +
            "\t`down_cdn9`\tstring,\n" +
            "\t`waste_bytes`\tstring,\n" +
            "\t`waste_bytes_all`\tstring,\n" +
            "\t`mona_conn_succ`\tdouble,\n" +
            "\t`mona_conn_succ_all`\tstring,\n" +
            "\t`down_yssdk`\tstring,\n" +
            "\t`down_yssdk_all`\tstring,\n" +
            "\t`bkj_has_closed`\tstring,\n" +
            "\t`skj_has_closed`\tstring,\n" +
            "\t`bkj_has_closed_all`\tstring,\n" +
            "\t`skj_has_closed_all`\tstring,\n" +
            "\t`using_peer_infos`\tstring,\n" +
            "\t`fps_origin`\tstring,\n" +
            "\t`bkj_recv_timeout`\tstring,\n" +
            "\t`skj_recv_timeout`\tstring,\n" +
            "\t`bkj_recv_timeout_all`\tstring,\n" +
            "\t`skj_recv_timeout_all`\tstring,\n" +
            "\t`sdk_platform`\tstring,\n" +
            "\t`down_cdn10`\tstring,\n" +
            "\t`down_cdn_3rd`\tdouble,\n" +
            "\t`down_cdn_3rd0`\tstring,\n" +
            "\t`down_cdn_3rd1`\tstring,\n" +
            "\t`down_cdn_3rd2`\tstring,\n" +
            "\t`down_cdn_3rd_all`\tstring,\n" +
            "\t`buffer_length`\tstring,\n" +
            "\t`unuse_chunk_buffer`\tstring,\n" +
            "\t`unuse_piece_buffer`\tstring,\n" +
            "\t`unuse_chunk_buffer_all`\tstring,\n" +
            "\t`unuse_piece_buffer_all`\tstring,\n" +
            "\t`http_host`\tstring,\n" +
            "\t`is_last_upload`\tstring,\n" +
            "\t`skj_tcp_connect_success`\tstring,\n" +
            "\t`skj_tcp_connect_failed`\tstring,\n" +
            "\t`skj_rtmfp_connect_success`\tstring,\n" +
            "\t`skj_rtmfp_connect_failed`\tstring,\n" +
            "\t`skj_tcp_connect_success_all`\tstring,\n" +
            "\t`skj_tcp_connect_failed_all`\tstring,\n" +
            "\t`skj_rtmfp_connect_success_all`\tstring,\n" +
            "\t`skj_rtmcp_connect_failed_all`\tstring,\n" +
            "\t`network_type`\tstring,\n" +
            "\t`app_behavior_id`\tstring,\n" +
            "\t`session_id`\tstring,\n" +
            "\t`first_upload`\tstring,\n" +
            "\t`accomplish_miss_times`\tstring,\n" +
            "\t`send_miss_piece_count`\tstring,\n" +
            "\tproctime as proctime(),\n" +
            "\twatermark for stime as stime - interval '10' second\n" +
            ") with (\n" +
            "    'properties.bootstrap.servers' = 'bdyun-suzhou-tw04a0264.su.baidu.internal:6667,bdyun-suzhou-tw04a0265.su.baidu.internal:6667,bdyun-suzhou-tw04d0849.su.baidu.internal:6667',\n" +
            "    'connector' = 'kafka',\n" +
            "    'format' = 'avro-event',\n" +
            "    'topic' = 'o_xylive_mobilesdk_cycle',\n" +
            "    'properties.group.id' = 'beta-sw-o_xylive_mobilesdk_cycle',\n" +
            "    'properties.security.protocol' = 'SASL_PLAINTEXT',\n" +
            "    'scan.startup.mode' = 'latest-offset',\n" +
            "    'properties.sasl.kerberos.service.name' = 'kafka'\n" +
            ");\n" +
            "\n" +
            "create table r_i1_live_mobile_sdk_detail_stat (\n" +
            "    `stime` timestamp,\n" +
            "    `domain`    string,\n" +
            "    `bw_cdn`    double,\n" +
            "    `bw_bkj`    double,\n" +
            "    `bw_skj`    double,\n" +
            "    `bw_fsdk`   double,\n" +
            "    `bw_3rd`    double,\n" +
            "    `skj_succ_cnt`  bigint,\n" +
            "    `skj_all_cnt`   bigint\n" +
            ") with (\n" +
            "    'database' = 'scdn_app_lo',\n" +
            "    'password' = 'rNXB>=dQCe3Ri-CGg*B?',\n" +
            "    'table-mode' = 'single',\n" +
            "    'sink.write-mode' = 'local',\n" +
            "    'connector' = 'clickhouse',\n" +
            "    'sink.flush-interval' = '60s',\n" +
            "    'sink.batch-size' = '10000',\n" +
            "    'url' = 'clickhouse://ck-lb.data.onething.internal:8123', \n" +
            "    'table' = 'r_i1_live_mobile_sdk_detail_stat',\n" +
            "    'username' = 'spark'\n" +
            ");\n" +
            "\n" +
            "\n" +
            "create table r_i1_live_mobile_sdk_band_stat (\n" +
            "    `stime` timestamp,\n" +
            "    `reg_id`    int,\n" +
            "    `isp`   int,\n" +
            "    `bw_3rd`    double,\n" +
            "    `bw_cdn`    double,\n" +
            "    `bw_bkj`    double,\n" +
            "    `bw_skj`    double,\n" +
            "    `bw_fsdk`   double,\n" +
            "    `mona_succ_cnt` double,\n" +
            "    `mona_all_cnt`  double,\n" +
            "    `bkj_succ_cnt`  double,\n" +
            "    `bkj_all_cnt`   double,\n" +
            "    `skj_succ_cnt`  double,\n" +
            "    `skj_all_cnt`   double\n" +
            ") with (\n" +
            "    'database'  =  'scdn_app_lo',\n" +
            "    'password'  =  'rNXB>=dQCe3Ri-CGg*B?',\n" +
            "    'table-mode'  =  'single',\n" +
            "    'sink.write-mode'  =  'local',\n" +
            "    'connector'  =  'clickhouse',\n" +
            "    'sink.flush-interval'  =  '60s',\n" +
            "    'sink.batch-size'  =  '10000',\n" +
            "    'url'  =  'clickhouse://ck-lb.data.onething.internal:8123',\n" +
            "    'table'  =  'r_i1_live_mobile_sdk_band_stat',\n" +
            "    'username'  =  'spark'\n" +
            ");\n" +
            "\n" +
            "\n" +
            "\n" +
            "insert into r_i1_live_mobile_sdk_detail_stat\n" +
            "select\n" +
            "    window_start as stime,\n" +
            "    `domain` as `domain`,\n" +
            "    sum(down_cdn) as bw_cdn,\n" +
            "    sum(down_bkj) as bw_bkj,\n" +
            "    sum(down_skj) as bw_skj,\n" +
            "    sum(down_fsdk) as bw_fsdk,\n" +
            "    sum(down_cdn_3rd) as bw_3rd,\n" +
            "    sum(skj_conn_succ) as skj_succ_cnt,\n" +
            "    sum(skj_conn_succ + skj_conn_fail) as skj_all_cnt\n" +
            "from table(tumble(table o_xylive_mobilesdk_cycle, descriptor(`stime`), interval '60' second))\n" +
            "group by\n" +
            "    `domain`,\n" +
            "    window_start,\n" +
            "    window_end\n" +
            "union all\n" +
            "select\n" +
            "    window_start as stime,\n" +
            "    '-9999' as domain,\n" +
            "    sum(down_cdn) as bw_cdn,\n" +
            "    sum(down_bkj) as bw_bkj,\n" +
            "    sum(down_skj) as bw_skj,\n" +
            "    sum(down_fsdk) as bw_fsdk,\n" +
            "    sum(down_cdn_3rd) as bw_3rd,\n" +
            "    sum(skj_conn_succ) as skj_succ_cnt,\n" +
            "    sum(skj_conn_succ + skj_conn_fail) as skj_all_cnt\n" +
            "from table(tumble(table o_xylive_mobilesdk_cycle, descriptor(`stime`), interval '60' second))\n" +
            "group by\n" +
            "    window_start,\n" +
            "    window_end;\n" +
            "\n" +
            "\n" +
            "\n" +
            "\n" +
            "\n" +
            "\n" +
            "\n" +
            "\n" +
            "\n" +
            "\n" +
            "create table d_scdn_region_province (\n" +
            "    `reg_id`    int,\n" +
            "    `prov_id`   int\n" +
            ") with (\n" +
            "    'connector'  =  'jdbc',\n" +
            "    'driver'  =  'com.mysql.jdbc.Driver',\n" +
            "    'lookup.cache.ttl'  =  '30min',\n" +
            "    'lookup.cache.max-rows'  =  '10000',\n" +
            "    'table-name'  =  'd_scdn_region_province',\n" +
            "    'url'  =  'jdbc:mysql://tidb-lb.data.onething.internal:4000/dim?useUnicode = true&useSSL = false&characterEncoding=UTF-8&allowMultiQueries = true&autoReconnect=true',\n" +
            "    'lookup.max-retries'  =  '3',\n" +
            "    'username' = 'beta',\n" +
            "    'password' = 'beta@9898w'\n" +
            ");\n" +
            "\n" +
            "\n" +
            "create view o_xylive_mobilesdk_cycle_filter as\n" +
            "select\n" +
            "    cpro,\n" +
            "    proctime,\n" +
            "    stime,\n" +
            "    cast((case when cisp in ('1','2','5') then cisp else '0' end) as int) isp,\n" +
            "    if(down_cdn_3rd = '\\N',0,down_cdn_3rd)     down_cdn_3rd,\n" +
            "    if(down_cdn = '\\N',0,down_cdn)             down_cdn,\n" +
            "    if(down_bkj = '\\N',0,down_bkj)             down_bkj,\n" +
            "    if(down_skj = '\\N',0,down_skj)             down_skj,\n" +
            "    if(down_fsdk = '\\N',0,down_fsdk)           down_fsdk,\n" +
            "    if(mona_conn_succ = '\\N',0,mona_conn_succ) mona_conn_succ,\n" +
            "    if(mona_conn_fail = '\\N',0,mona_conn_fail) mona_conn_fail,\n" +
            "    if(bkj_conn_succ = '\\N',0,bkj_conn_succ)   bkj_conn_succ,\n" +
            "    if(bkj_conn_fail = '\\N',0,bkj_conn_fail)   bkj_conn_fail,\n" +
            "    if(skj_conn_succ = '\\N',0,skj_conn_succ)   skj_conn_succ,\n" +
            "    if(skj_conn_fail = '\\N',0,skj_conn_fail)   skj_conn_fail\n" +
            "from o_xylive_mobilesdk_cycle\n" +
            "where `domain` <> '\\N' and `app` <> '\\N' and `stream` <> '\\N';\n" +
            "\n" +
            "create view o_xylive_mobilesdk_cycle_filter_join as\n" +
            "select\n" +
            "    a.*,\n" +
            "    ifnull(b.reg_id, -9995) AS reg_id\n" +
            "from o_xylive_mobilesdk_cycle_filter a\n" +
            "left join d_scdn_region_province for system_time as of a.proctime b on a.cpro  =  b.prov_id;\n" +
            "\n" +
            "insert into r_i1_live_mobile_sdk_band_stat\n" +
            "select\n" +
            "    window_start as stime,\n" +
            "    ifnull(reg_id, -9995) AS reg_id,\n" +
            "    isp,\n" +
            "    sum(down_cdn_3rd) * 8 / 60 AS      bw_3rd,\n" +
            "    sum(down_cdn) * 8 / 60 AS          bw_cdn,\n" +
            "    sum(down_bkj) * 8 / 60 AS          bw_bkj,\n" +
            "    sum(down_skj) * 8 / 60 AS          bw_skj,\n" +
            "    sum(down_fsdk) * 8 / 60 AS         bw_fsdk,\n" +
            "    sum(mona_conn_succ)                mona_succ_cnt,\n" +
            "    sum(mona_conn_succ+mona_conn_fail) mona_all_cnt,\n" +
            "    sum(bkj_conn_succ)                 bkj_succ_cnt,\n" +
            "    sum(bkj_conn_succ+bkj_conn_fail)   bkj_all_cnt,\n" +
            "    sum(skj_conn_succ)                 skj_succ_cnt,\n" +
            "    sum(skj_conn_succ+skj_conn_fail)   skj_all_cnt\n" +
            "from table(tumble(table o_xylive_mobilesdk_cycle_filter_join, descriptor(`stime`), interval '60' second))\n" +
            "group by reg_id, isp, window_start, window_end;";
}
